package cn.chint.manage.dao.impl;

import java.math.BigInteger;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Repository;

import cn.chint.bo.MsgEnterPages;
import cn.chint.bo.MsgEnterTempbo;
import cn.chint.po.MsgEnter;
import cn.chint.po.NoticeUser;
@Repository
public class MsgenterDaoImpl implements cn.chint.manage.dao.MsgenterDao {
	@Autowired
	HibernateTemplate hibernateTemplate;
	public Logger log = Logger.getLogger(getClass());
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public void savemsg(MsgEnter m) {
		log.info("执行到Dao层了");
		this.hibernateTemplate.save(m);
	}
	
	@SuppressWarnings("rawtypes")
	public MsgEnterPages find_msg_enter(final int page,final int size) {
		List<MsgEnterTempbo> ls  = (List<MsgEnterTempbo>) this.hibernateTemplate.execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				String s="SELECT msg.*,usr.wx_nickname as 'nickname' from (SELECT m.*,u.wx_nickname as 'zf_name' from msg_enter m LEFT JOIN t_user   u   on (m.zf_openid = u.wx_open_id)  where  m.pseudo=1) msg ,t_user usr" 
						+" where  msg.openid = usr.wx_open_id order by createTime desc  ";
				return session.createSQLQuery(s).
				addScalar("id",StandardBasicTypes.LONG).
			    addScalar("cityid",StandardBasicTypes.STRING).
			    addScalar("communic",StandardBasicTypes.STRING).
			    addScalar("money",StandardBasicTypes.STRING).
			    addScalar("customer_type",StandardBasicTypes.STRING).
			    addScalar("openid",StandardBasicTypes.STRING).
			    addScalar("project_description",StandardBasicTypes.STRING).
			    addScalar("project_progress",StandardBasicTypes.STRING).
			    addScalar("project_start_time",StandardBasicTypes.STRING).
			    addScalar("provid",StandardBasicTypes.STRING).
			    addScalar("provider_name",StandardBasicTypes.STRING).
			    addScalar("provider_phone_number",StandardBasicTypes.STRING).
			    addScalar("review",StandardBasicTypes.STRING).
			    addScalar("scale",StandardBasicTypes.STRING).
			    addScalar("trial",StandardBasicTypes.STRING).
			    addScalar("file_url",StandardBasicTypes.STRING).
			    addScalar("money",StandardBasicTypes.STRING).
			    addScalar("orderNo",StandardBasicTypes.STRING).
			    addScalar("project_name",StandardBasicTypes.STRING).
			    addScalar("truth_type",StandardBasicTypes.STRING).
			    addScalar("reject_reason",StandardBasicTypes.STRING).
			    addScalar("channel",StandardBasicTypes.STRING).
			    addScalar("customer_type_product",StandardBasicTypes.STRING).
			    addScalar("like_name",StandardBasicTypes.STRING).
			    addScalar("like_value",StandardBasicTypes.STRING).
			    addScalar("pseudo",StandardBasicTypes.LONG).
			    addScalar("zf_openid",StandardBasicTypes.STRING).
			    addScalar("nickname",StandardBasicTypes.STRING).
			    addScalar("zf_name",StandardBasicTypes.STRING).
			    addScalar("createTime",StandardBasicTypes.STRING).
			    setResultTransformer(Transformers.aliasToBean(MsgEnterTempbo.class)).setFirstResult((page-1)*size).setMaxResults(size).list();		
			}
		});
		
		@SuppressWarnings("unchecked")
		BigInteger count = (BigInteger)this.hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createSQLQuery("select count(*) from msg_enter t where pseudo = 1").uniqueResult();			
			}
		
		});
		MsgEnterPages  msgEnterPages =new MsgEnterPages();
		msgEnterPages.setLs(ls);
		msgEnterPages.setMsgenter_num(count.intValue());
		return msgEnterPages;
		
	}
//利用项目名称进行模糊查询
    @Override
	public MsgEnterPages find_msg_enter_by_name(final int page,final int size,final String channelUser) {
		// TODO Auto-generated method stub

		@SuppressWarnings("unchecked")
		BigInteger count = (BigInteger)this.hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createSQLQuery("select count(*) from msg_enter t where pseudo = 1 and t.project_name LIKE '%"+channelUser+"%'").uniqueResult();
			}

		});

		final int cou=count.intValue();

		@SuppressWarnings({ "unchecked", "rawtypes" })
		List<MsgEnterTempbo> ls  = (List<MsgEnterTempbo>) this.hibernateTemplate.execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				String s="SELECT msg.*,usr.wx_nickname as 'nickname' from (SELECT m.*,u.wx_nickname as 'zf_name' from msg_enter m LEFT JOIN t_user   u   on (m.zf_openid = u.wx_open_id)  where  m.pseudo=1) msg ,t_user usr" 
						+" where msg.openid = usr.wx_open_id AND msg.project_name LIKE '%"+channelUser+"%' order by createTime desc  ";

				int page1=page;
				if(cou/size<page){
					page1=cou/size+1;
				}

		//		return session.createSQLQuery("SELECT msg.*,usr.wx_nickname as '提交用户' from (SELECT m.*,u.wx_nickname as '转发用户' from msg_enter m LEFT JOIN t_user   u   on (m.zf_openid = u.wx_open_id)  where  m.pseudo=1) msg ,t_user usr where  msg.openId = usr.wx_open_id").addEntity(MsgEnterTempbo.class).setFirstResult((page-1)*size).setMaxResults(size).list();
				return session.createSQLQuery(s).  
				addScalar("id",StandardBasicTypes.LONG).
			    addScalar("cityid",StandardBasicTypes.STRING).
			    addScalar("communic",StandardBasicTypes.STRING).
			    addScalar("money",StandardBasicTypes.STRING).
			    addScalar("customer_type",StandardBasicTypes.STRING).
			    addScalar("openid",StandardBasicTypes.STRING).
			    addScalar("project_description",StandardBasicTypes.STRING).
			    addScalar("project_progress",StandardBasicTypes.STRING).
			    addScalar("project_start_time",StandardBasicTypes.STRING).
			    addScalar("provid",StandardBasicTypes.STRING).
			    addScalar("provider_name",StandardBasicTypes.STRING).
			    addScalar("provider_phone_number",StandardBasicTypes.STRING).
			    addScalar("review",StandardBasicTypes.STRING).
			    addScalar("scale",StandardBasicTypes.STRING).
			    addScalar("trial",StandardBasicTypes.STRING).
			    addScalar("file_url",StandardBasicTypes.STRING).
			    addScalar("money",StandardBasicTypes.STRING).
			    addScalar("orderNo",StandardBasicTypes.STRING).
			    addScalar("project_name",StandardBasicTypes.STRING).
			    addScalar("truth_type",StandardBasicTypes.STRING).
			    addScalar("reject_reason",StandardBasicTypes.STRING).
			    addScalar("channel",StandardBasicTypes.STRING).
			    addScalar("customer_type_product",StandardBasicTypes.STRING).
			    addScalar("like_name",StandardBasicTypes.STRING).
			    addScalar("like_value",StandardBasicTypes.STRING).
			    addScalar("pseudo",StandardBasicTypes.LONG).
			    addScalar("zf_openid",StandardBasicTypes.STRING).
			    addScalar("nickname",StandardBasicTypes.STRING).
			    addScalar("zf_name",StandardBasicTypes.STRING).
			    addScalar("createTime",StandardBasicTypes.STRING).
			    setResultTransformer(Transformers.aliasToBean(MsgEnterTempbo.class)).setFirstResult((page1-1)*size).setMaxResults(size).list();
			}
		});
		

		MsgEnterPages  msgEnterPages =new MsgEnterPages();
		msgEnterPages.setLs(ls);
		msgEnterPages.setMsgenter_num(count.intValue());
		return msgEnterPages;
		
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public MsgEnter updatetrial( final long id, final String trial){
		
		 this.hibernateTemplate.execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createQuery("update MsgEnter set trial=:trial where  id=:id")
						.setLong("id", id).setString("trial", trial).executeUpdate();
			}
		});
		return null;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public MsgEnter updatereview( final long id, final String review){
		
		 this.hibernateTemplate.execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createQuery("update MsgEnter set review=:review where  id=:id")
						.setLong("id", id).setString("review", review).executeUpdate();
			}
		});
		return null;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public MsgEnter reject_pass( final long id, final String reject_reason,final String review){
		
		 this.hibernateTemplate.execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createQuery("update MsgEnter set reject_reason=:reject_reason ,review=:review  where  id=:id").setLong("id", id).setString("reject_reason", reject_reason)
						.setString("review", review).executeUpdate();
			}
		});
		return null;
	}
	
	@Override
	public MsgEnterTempbo getMsgEnter(final long id) {
		// TODO Auto-generated method stub
		@SuppressWarnings({ "unchecked", "rawtypes" })
		MsgEnterTempbo ls  =(MsgEnterTempbo)this.hibernateTemplate.execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				String s="SELECT msg.*,usr.wx_nickname as 'nickname' from (SELECT m.*,u.wx_nickname as 'zf_name' from msg_enter m LEFT JOIN t_user   u   on (m.zf_openid = u.wx_open_id)  where  m.pseudo=1) msg ,t_user usr" 
						+" where  msg.openid = usr.wx_open_id and msg.id=:id order by createTime desc  ";
				return session.createSQLQuery(s).
				addScalar("id",StandardBasicTypes.LONG).
			    addScalar("cityid",StandardBasicTypes.STRING).
			    addScalar("communic",StandardBasicTypes.STRING).
			    addScalar("money",StandardBasicTypes.STRING).
			    addScalar("customer_type",StandardBasicTypes.STRING).
			    addScalar("openid",StandardBasicTypes.STRING).
			    addScalar("project_description",StandardBasicTypes.STRING). 
			    addScalar("project_progress",StandardBasicTypes.STRING).
			    addScalar("project_start_time",StandardBasicTypes.STRING).
			    addScalar("provid",StandardBasicTypes.STRING).
			    addScalar("provider_name",StandardBasicTypes.STRING).
			    addScalar("provider_phone_number",StandardBasicTypes.STRING).
			    addScalar("review",StandardBasicTypes.STRING).
			    addScalar("scale",StandardBasicTypes.STRING).
			    addScalar("trial",StandardBasicTypes.STRING).
			    addScalar("file_url",StandardBasicTypes.STRING).
			    addScalar("money",StandardBasicTypes.STRING).
			    addScalar("orderNo",StandardBasicTypes.STRING).
			    addScalar("project_name",StandardBasicTypes.STRING).
			    addScalar("truth_type",StandardBasicTypes.STRING).
			    addScalar("reject_reason",StandardBasicTypes.STRING).
			    addScalar("channel",StandardBasicTypes.STRING).
			    addScalar("customer_type_product",StandardBasicTypes.STRING).
			    addScalar("like_name",StandardBasicTypes.STRING).
			    addScalar("like_value",StandardBasicTypes.STRING).
			    addScalar("pseudo",StandardBasicTypes.LONG).
			    addScalar("zf_openid",StandardBasicTypes.STRING).
			    addScalar("nickname",StandardBasicTypes.STRING).
			    addScalar("zf_name",StandardBasicTypes.STRING).
			    addScalar("createTime",StandardBasicTypes.STRING).
			    setResultTransformer(Transformers.aliasToBean(MsgEnterTempbo.class)).setLong("id", id).uniqueResult();	
			}
		});
		return ls;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public void Update(final String money,final long id) {
		this.hibernateTemplate.execute(new HibernateCallback() {

			@Override
			public Object doInHibernate(Session session)
					throws HibernateException {
						
				return session.createQuery("update MsgEnter set money=:money where id=:id")
						.setString("money", money).setLong("id",id).executeUpdate();
			}
		});
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<MsgEnter> Select(final String provid,
			final String cityid) {
//		List<MsgEnter> ls = this.hibernateTemplate.execute(new HibernateCallback() {
//			@Override
//			public Object doInHibernate(Session session)
//					throws HibernateException {
//				return session.createSQLQuery("select * from msg_enter  where provid=:provid and cityid=:cityid")
//						.addEntity(MsgEnter.class).setString("provid", provid).setString("cityid", cityid).list();
//			}
//		});
		List<MsgEnter> ls = (List<MsgEnter>) this.hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException {
				return session.createSQLQuery("select * from msg_enter")
						.addEntity(MsgEnter.class).list();
			}
		});
		return ls;
	}

	@Override
	public NoticeUser findNoticeUser(final String type) {
		@SuppressWarnings({ "unchecked", "rawtypes" })
		List<NoticeUser> ls  = (List<NoticeUser>) this.hibernateTemplate.execute(new HibernateCallback() {
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createSQLQuery("select * from notice_user t where t.type=:type").addEntity(NoticeUser.class).setString("type", type).list();
						
			}
		});
		return ls.get(0);
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public void UpdPseudo(final long id, final long pseudo) {
		this.hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session)
					throws HibernateException {
				return session.createQuery("Update MsgEnter set pseudo=:pseudo where id=:id")
						.setLong("id", id).setLong("pseudo", pseudo).executeUpdate();
			}
		});
	}
}


















